Dynamic Data Mask is now useful and no one noticed it

Dynamic data mask is a very interesting security feature allowing us to mask critical fields such as e-mail, phone number, credit card and so on. We can decide what users will be able to see the value of these features or not.

This feature faced many flaws when it was released, but I believe it’s stable now, although It’s not the main security feature you should care about, it can still be very useful.

However, until very recently, this feature was not very useful. If you mask many fields in many different tables, the fields may require different permission levels in order to be unmasked.

The only permission control provided for Mask and Unmask fields where this: See everything masked or everything unmasked. In this way, the feature was not useful, because there is not doubt that phone number and credit card should have different permission levels.

Finally, the granular permission control for Data Mask is available in Azure SQL Databases. The feature became way more useful and with so many news around these weeks, not many people noticed.

Test Environment

Let’s test this feature using the AdventureWorksLT. We can provision this database in Azure SQL by choosing to provision a sample database.

Applying the Data Mask

alter table SalesLT.Customer
alter column EmailAddress nvarchar(50)
masked with (function=’email()’)

alter table SalesLT.Customer
alter column Phone nvarchar(25)
masked with(function=‘partial(3,”XXXXXXXXX”,0)’)

Create Roles to control Mask Permissions

Control permissions in a field level is something complex. A good practice to do this is using database roles. Let’s create database roles for this and set the permission of these roles as data reader to make the example easier.

Create Role EmailView

Create Role PhoneView

Alter Role db_datareader add member EmailView

Alter Role db_datareader add member PhoneView

Grant the unmask peremission

On our example, we will grant the unmask permission over each field for the different database roles. The statements will be like this:

Grant UnMask on SalesLT.Customer(Phone) to PhoneView
go
Grant UnMask on SalesLT.Customer(EmailAddress) to Emailview
go

The granular unmask permission also would allow to grant permission schema level:

Grant UnMask on Schema::SalesLT to CustomRole

Or also on table level:

Grant UnMask on SalesLT.Customer to CustomRole

Anyway, now making the data mask feature really useful.

Create user CanReadEmail with password=‘9646xpahmW’

Create user CanReadPhone with password=‘9646xpahmW’

alter role EmailView add member CanReadEmail

alter role PhoneView add member CanReadPhone

Test the users and UnMask Feature

Execute as user=‘CanReadEmail’

select * from SalesLT.Customer

revert

This first user can read the phone, but not the e-mail:

Execute as user=‘CanReadPhone’

select * from SalesLT.Customer

revert

This 2nd user can read the e-mail but not the phone:

Conclusion

We have a new powerful security feature on our hands to work with and I hope this feature to be in SQL Server 2022 as well

Register for Simple Talk

Dynamic Data Mask is now useful and no one noticed it

Test Environment